package com.share.poi.excel;

import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * Created by caifan on 2019/6/25.
 */
public class POIExportUtils {

    /**
     * 传入的数据必须是List<Map<String, Object>>格式，list中数据第一个索引的为标题，其他的是数据
     * @param fileName
     * @param dir
     * @param datas
     */
    public static void createExcel(String fileName, String dir, List<Map<Integer, Object>> datas, List<Integer> hyperLinkColumns, Map<Integer, String> hyperLinkPrefix) throws Exception {
        if (dir == null || dir.trim().length() == 0) {
            dir = System.getProperty("user.home");
            File fileDir = new File(dir + File.separator + "/cashbus");
            fileDir.mkdirs();
            dir = fileDir.getPath();
        }
        if(fileName == null || fileName.trim().length() == 0) {
            fileName = System.currentTimeMillis() + "";
        }

        FileOutputStream fos = new FileOutputStream(dir + File.separator + fileName + ".xlsx");
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(fileName);

        Map<String, CellStyle> styles = createStyles(workbook);

        createTitle(sheet, datas.get(0), styles.get("header"));
        Map<Integer, Object> dataMap;
        Row dataRow;
        for (int i = 1;i < datas.size();i++) {
            dataMap = datas.get(i);
            dataRow = sheet.createRow(i);
            addData(workbook, sheet, dataRow, dataMap, styles, hyperLinkColumns, hyperLinkPrefix);

        }
        workbook.write(fos);
        fos.close();
        workbook.close();

    }

    /**
     * 单元格中添加数据，其中超链接列标号与超链接前缀对应的值相同
     * @param workbook
     * @param sheet
     * @param dataRow
     * @param dataMap 数据
     * @param styles  单元格样式列表
     * @param hyperLinkColumns 超链接所在的列标号
     * @param hyperLinkPrefix  超链接的前缀
     */
    private static void addData(Workbook workbook, Sheet sheet, Row dataRow, Map<Integer, Object> dataMap, Map<String, CellStyle> styles, List<Integer> hyperLinkColumns, Map<Integer, String> hyperLinkPrefix) {
        CreationHelper helper = workbook.getCreationHelper();
        Cell cell;
        if(dataMap == null || dataMap.size() == 0) {
            return ;
        }
        for (int i=0;i<dataMap.size();i++) {
            cell = dataRow.createCell(i);
            if (dataMap.get(i+1) != null) {
                //设置超链接的列
                if (hyperLinkColumns.contains(i+1)) {
//                    Hyperlink link = (XSSFHyperlink)helper.createHyperlink(Hyperlink.LINK_URL);
                    Hyperlink link = (XSSFHyperlink)helper.createHyperlink(HyperlinkType.URL);
                    sheet.autoSizeColumn(i);
                    link.setAddress(hyperLinkPrefix.get(i+1) + dataMap.get(i+1).toString().trim());
                    cell.setHyperlink(link);
                    cell.setCellValue(dataMap.get(i+1).toString().trim());
                    cell.setCellStyle(styles.get("hlinkStyle"));
                } else {
                    cell.setCellValue(dataMap.get(i+1).toString().trim());
                    cell.setCellStyle(styles.get("contentStyle"));
                }
            } else {
                cell.setCellStyle(styles.get("contentStyle"));
            }
        }
    }

    /**
     * 生成表头
     * @param sheet
     * @param titleMap
     * @param style
     */
    private static void createTitle(Sheet sheet, Map<Integer, Object> titleMap, CellStyle style) {
        Row headerRow = sheet.createRow(0);
        Cell cell;
        for (int i=0;i<titleMap.size();i++) {
            sheet.autoSizeColumn(i);
            cell = headerRow.createCell(i);
            cell.setCellValue(titleMap.get(i+1).toString());
            cell.setCellStyle(style);
        }
    }

    /**
     * 单元格样式设置
     */
    private static Map<String, CellStyle> createStyles(Workbook workbook){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

        CellStyle cellStyle;
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short)12);
        headerFont.setBold(true);
        cellStyle = createBorderedStyle(workbook);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFont(headerFont);
        styles.put("header", cellStyle);

        Font hlinkFont = workbook.createFont();
        hlinkFont.setUnderline(Font.U_SINGLE);
        hlinkFont.setColor(Font.COLOR_NORMAL);
        cellStyle =  createBorderedStyle(workbook);
        cellStyle.setFont(hlinkFont);
        styles.put("hlinkStyle", cellStyle);

        Font contentFont = workbook.createFont();
        contentFont.setFontHeightInPoints((short)12);
        cellStyle = createBorderedStyle(workbook);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(contentFont);
        cellStyle.setWrapText(true);
        styles.put("contentStyle", cellStyle);

        return styles;
    }

    private static CellStyle createBorderedStyle(Workbook workbook){
        BorderStyle thin = BorderStyle.THIN;
        short black = IndexedColors.BLACK.getIndex();

        CellStyle style = workbook.createCellStyle();
        style.setBorderRight(thin);
        style.setRightBorderColor(black);
        style.setBorderBottom(thin);
        style.setBottomBorderColor(black);
        style.setBorderLeft(thin);
        style.setLeftBorderColor(black);
        style.setBorderTop(thin);
        style.setTopBorderColor(black);
        //设置居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

}